package com.demo2do.fms.admin.entity.search.business;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 * 报销查询
 * 
 * @author Ark
 *
 */
public class ReimburseSearch implements QueryBuilder {
    
    /** 所选择公司 */
    private Long organizationId;
    
    /** 驳运车号 */
    private Long vehicleId;
    
    /** 计划出发日期-开始日期 */
    private String beginDate;
    
    /** 计划出发日期-截止日期 */
    private String endDate;
    
    private QueryOrder queryOrder;
    
    private QueryFilter queryFilter;
    
    /**
     * The default constructor
     */
    public ReimburseSearch() {
        queryOrder = new QueryOrder("leavingTime", "ASC");
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter().addEquals("a", "organization_id", organizationId)
                                           .addEquals("a", "vehicle_id", vehicleId);
            
            if (StringUtils.isNotBlank(beginDate)) {
                queryFilter.addSentence("a.leaving_time >= TO_DATE('" + beginDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endDate)) {
                queryFilter.addSentence("a.leaving_time < TO_DATE('" + endDate.replace("-", "") + "', 'yyyymmdd') + TO_DSINTERVAL ('001 00:00:00')", Collections.emptyMap());
            }
            
        }
        
        return queryFilter;
        
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }
    
    /**
     * @return
     */
    public String buildCountTemplate() {
        String sentence = "SELECT COUNT(*) FROM vw_dispatch_order a, tbl_truck_transport_cost c, vw_transportation_company o WHERE a.id = c.dispatch_order_id AND a.organization_id = o.id AND a.is_expense_reimbursed = 0" + getPreparedFilter(false).clauseSentence();
        return sentence;
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT a.id, ")
                      .append("o.organization_name AS organizationName, ")
                      .append("a.dispatch_order_code AS dispatchOrderCode, ")
                      .append("a.start_facility_name AS startFacilityName, ")
                      .append("a.end_facility_name AS endFacilityName, ")
                      .append("a.leaving_time AS leavingTime, ")
                      .append("a.actual_load_number AS actualLoadNumber, ")
                      .append("c.full_load_distance AS fullLoadDistance, ")
                      .append("c.empty_load_distance AS emptyLoadDistance, ")
                      .append("c.road_fee AS roadFee, ")
                      .append("FC_GET_OTHER_FEE(a.id) AS otherFee, ")
                      .append("c.oil_usage AS oilUsage, ")
                      .append("c.internal_oil_price AS internalOilPrice, ")
                      .append("c.externaloil_price AS externalOilPrice, ")
                      .append("c.full_oil_usage_per_km AS fullOilUsagePerKm, ")
                      .append("c.empty_oil_usage_per_km AS emptyOilUsagePerKm ")
              .append("FROM vw_dispatch_order a, tbl_truck_transport_cost c, vw_transportation_company o ")
              .append("WHERE a.id = c.dispatch_order_id AND a.organization_id = o.id AND a.is_expense_reimbursed = 0 ")
              .append(getPreparedFilter(false).clauseSentence() + queryOrder.sentence());

        return buffer.toString();
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("id", rs.getObject("id"));
                result.put("organizationName", rs.getObject("organizationName"));
                result.put("dispatchOrderCode", rs.getObject("dispatchOrderCode"));
                result.put("startFacilityName", rs.getObject("startFacilityName"));
                result.put("endFacilityName", rs.getObject("endFacilityName"));
                result.put("leavingTime", rs.getObject("leavingTime"));
                result.put("actualLoadNumber", rs.getObject("actualLoadNumber"));
                result.put("fullLoadDistance", rs.getObject("fullLoadDistance"));
                result.put("emptyLoadDistance", rs.getObject("emptyLoadDistance"));
                result.put("roadFee", rs.getObject("roadFee"));
                result.put("otherFee", rs.getObject("otherFee"));
                result.put("oilUsage", rs.getObject("oilUsage"));
                result.put("internalOilPrice", rs.getObject("internalOilPrice"));
                result.put("externalOilPrice", rs.getObject("externalOilPrice"));
                result.put("fullOilUsagePerKm", rs.getObject("fullOilUsagePerKm"));
                result.put("emptyOilUsagePerKm", rs.getObject("emptyOilUsagePerKm"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /**
     * @return
     */
    public QueryFilter getFilter(){
        return getPreparedFilter(false);
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return the vehicleId
     */
    public Long getVehicleId() {
        return vehicleId;
    }

    /**
     * @return
     */
    public String getBeginDate() {
        return beginDate;
    }
    
    /**
     * @return
     */
    public String getEndDate() {
        return endDate;
    }

    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param vehicleId the vehicleId to set
     */
    public void setVehicleId(Long vehicleId) {
        this.vehicleId = vehicleId;
    }

    /**
     * @param beginDate
     */
    public void setBeginDate(String beginDate) {
        this.beginDate = beginDate;
    }
    
    /**
     * @param endDate
     */
    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }
    
}
